In [1]:
%matplotlib inline
import numpy as np
import matplotlib.pyplot as plt

In [2]:
from openpyxl import load_workbook
from openpyxl import Workbook

In [3]:
from openpyxl.styles import Color, PatternFill, Font, Border, Side

Paso 0: definir algunas funciones


In [15]:
def buscaproob(text):
    prov = 'P'
    text = text.upper()
    for char_num in range(len(text)-1):
        char = text[char_num]
        if char == 'P' :
            ii = 1
            while char_num+ii<len(text) and text[char_num+ii].isdigit():
                prov += text[char_num+ii]
                ii += 1
            if prov != 'P':
                return prov
    return ''
            
def str_to_bool(text):
    text = text.lower()
    if text == 'sí' or text == 'si' or text =='s':
        return True
    elif text == 'no' or text == 'n':
        return False
    else:
        error_msg = 'Encontrado error en booleano:' + str(text)
        raise ValueError(error_msg)

Paso 1: combinar los 4 excel y distribuir los datos


In [5]:
wb_1 = load_workbook(filename='Informe de precios-1.xlsx', read_only=True)
wb_2 = load_workbook(filename='Informe de precios-2.xlsx', read_only=True)
wb_3 = load_workbook(filename='Informe de precios-3.xlsx', read_only=True)
wb_4 = load_workbook(filename='Informe de precios-4.xlsx', read_only=True)
ws_1 = wb_1[wb_1.sheetnames[0]]
ws_2 = wb_2[wb_2.sheetnames[0]]
ws_3 = wb_3[wb_3.sheetnames[0]]
ws_4 = wb_4[wb_4.sheetnames[0]]

In [6]:
ws_list = [ws_1, ws_2, ws_3,ws_4]

In [7]:
raw_data_matrix = []
for doc in range(4):
    raw_data = []
    ws = ws_list[doc]
    for row in ws:
        row_current = []
        for cell in row:
            row_current.append(cell.value)
        raw_data_matrix.append(row_current)
raw_data_array = np.array(raw_data_matrix)

In [8]:
raw_data_array


Out[8]:
array([['Lista PVP3', '0112-06-00', 2.82],
       ['Lista PVP3', '0112-08-00', 3.42],
       ['Lista PVP3', '0112-10-00', 4.5],
       ..., 
       ['Último precio de compra', 'ZRE-6-A', None],
       ['Último precio de compra', 'ZRE-6-AB', None],
       ['Último precio de compra', 'ZRE-6-B', 35.64]], dtype=object)

In [9]:
for row in range(raw_data_array.shape[0]):
    raw_data_array[row,1] = str(raw_data_array[row,1])

In [10]:
p1_matrix = []
p2_matrix = []
p3_matrix = []
p4_matrix = []
last_matrix = []
prov_matrix = []
other_matrix =[]
for row_num in range(raw_data_array.shape[0]):
    row = raw_data_array[row_num,:]
    if row[0] == 'Lista PVP1':
        p1_matrix.append(row)
    elif row[0] == 'Lista PVP2':
        p2_matrix.append(row)
    elif row[0] == 'Lista PVP3':
        p3_matrix.append(row)
    elif row[0] == 'Lista PVP4':
        p4_matrix.append(row)
    elif row[0] == 'Último precio de compra':
        last_matrix.append(row)
    elif row[0][0] == 'P':
        prov_matrix.append(row)
    else:
        other_matrix.append(row)

In [11]:
p1_matrix = np.array(p1_matrix)
p2_matrix = np.array(p2_matrix)
p3_matrix = np.array(p3_matrix)
p4_matrix = np.array(p4_matrix)
last_matrix = np.array(last_matrix)
prov_matrix = np.array(prov_matrix)
other_matrix = np.array(other_matrix)

Paso 2 : crear matriz de PVP


In [12]:
wb_articulos = load_workbook(filename='Lista de Artículos.xlsx', read_only=True)
ws_articulos = wb_articulos[wb_articulos.sheetnames[0]]

In [13]:
articulos_data_matrix = []
for row in ws_articulos:
    row_current = []
    for cell in row:
        row_current.append(cell.value)
    articulos_data_matrix.append(row_current)
articulos_data_array = np.array(articulos_data_matrix)

In [16]:
bool_array = []
prov_array = []
for row in range(articulos_data_array.shape[0]):
    articulos_data_array[row,0] = str(articulos_data_array[row,0])
    articulos_data_array[row,2] = str(articulos_data_array[row,2])
    
    bool_data = str_to_bool(articulos_data_array[row,1])
    bool_array.append(bool_data)
    prov_data = buscaproob(articulos_data_array[row,2])
    prov_array.append(prov_data)

bool_array = np.array(bool_array)
prov_array = np.array(prov_array)

In [17]:
articulos_pvp_array = np.zeros((articulos_data_array.shape[0],5),dtype='O')

In [18]:
articulos_pvp_array[:,0] = articulos_data_array[:,0]

In [19]:
articulos_pvp_array[:20,]


Out[19]:
array([['000000.3206.110.50', 0, 0, 0, 0],
       ['0112-06-00', 0, 0, 0, 0],
       ['0112-08-00', 0, 0, 0, 0],
       ['0112-10-00', 0, 0, 0, 0],
       ['0126-08-00', 0, 0, 0, 0],
       ['0160-MA-010-BN', 0, 0, 0, 0],
       ['02000-04', 0, 0, 0, 0],
       ['02000-06', 0, 0, 0, 0],
       ['02000-10', 0, 0, 0, 0],
       ['208025', 0, 0, 0, 0],
       ['208027', 0, 0, 0, 0],
       ['226005600', 0, 0, 0, 0],
       ['0240D010BN4HC', 0, 0, 0, 0],
       ['025-CS-200', 0, 0, 0, 0],
       ['025-CS-250', 0, 0, 0, 0],
       ['3040402', 0, 0, 0, 0],
       ['3041002', 0, 0, 0, 0],
       ['31001', 0, 0, 0, 0],
       ['31002', 0, 0, 0, 0],
       ['31003', 0, 0, 0, 0]], dtype=object)

In [20]:
p1_errores = []
p2_errores = []
p3_errores = []
p4_errores = []
pvp_list = [p1_matrix,
            p2_matrix,
            p3_matrix,
            p4_matrix]
pvp_error_list = [p1_errores,
                  p2_errores,
                  p3_errores,
                  p4_errores]
for pvp_num in range(4):
    print('calculando PVP', pvp_num + 1, 'antiguo')
    for row in range(pvp_list[pvp_num].shape[0]):
        name = pvp_list[pvp_num][row, 1]
        cost = pvp_list[pvp_num][row, 2]
        if name in articulos_pvp_array[:,0]:
            loc = np.where(articulos_pvp_array[:,0]==name)[0][0]
            articulos_pvp_array[loc, pvp_num + 1] = cost
        else:
            pvp_error_list[pvp_num].append(pvp_list[pvp_num][row, :])
p1_errores = np.array(p1_errores)
p2_errores = np.array(p2_errores)
p3_errores = np.array(p3_errores)
p4_errores = np.array(p4_errores)
print('completado')


calculando PVP 1 antiguo
calculando PVP 2 antiguo
calculando PVP 3 antiguo
calculando PVP 4 antiguo
completado

Definir precio base de compra


In [21]:
prov_cost_dict = {}
for row_num in range(prov_matrix.shape[0]):
    row = prov_matrix[row_num,:]
    name = row[1]
    cost = row[2]
    if name in prov_cost_dict :
        prov_cost_dict[name].append(cost)
    else:
        prov_cost_dict[name] = [cost]

In [22]:
def calc_precio_base(lista_precios):
    return np.round(np.mean(lista_precios), decimals=2)

In [23]:
precio_base_array = []
for name in prov_cost_dict :
    cost = calc_precio_base(prov_cost_dict[name])
    precio_base_array.append([name, cost])
    
precio_base_array = np.array(precio_base_array)
precio_compra_array = precio_base_array

In [24]:
last_matrix_new =[]
last_matrix_error = []
for row_num in range(last_matrix.shape[0]):
    row = last_matrix[row_num,1:]
    if row[1] != None:
        last_matrix_new.append(row)
    else: 
        last_matrix_error.append(row)

last_matrix_new=np.array(last_matrix_new)
last_matrix_error = np.array(last_matrix_error)
last_matrix = last_matrix_new

In [25]:
art_in_last_not_in_prov = []
for row_num in range(last_matrix_new.shape[0]):
    row = last_matrix_new[row_num,:]
    name = row[0]
    cost = row[1]
    if not(name in precio_base_array[:,0]):
        if cost != 0:
            art_in_last_not_in_prov.append(row)
art_in_last_not_in_prov = np.array(art_in_last_not_in_prov)

In [26]:
art_in_prov_not_in_last = []
for row_num in range(precio_base_array.shape[0]):
    row = precio_base_array[row_num,:]
    name = row[0]
    cost = row[1]
    if not(name in last_matrix_new[:,0]):
        if cost != 0:
            art_in_prov_not_in_last.append(row)
art_in_prov_not_in_last = np.array(art_in_prov_not_in_last)

In [27]:
alertas_dict = {}
text = 'artículo con precio de compra pero no de proveedor'
for row in range(art_in_last_not_in_prov.shape[0]):
    name = art_in_last_not_in_prov[row,0]
    alertas_dict[name] = [text]

In [28]:
text = 'artículo con precio de proovedor pero no de compra'
for row in range(art_in_prov_not_in_last.shape[0]):
    name = art_in_prov_not_in_last[row,0]
    alertas_dict[name] = [text]

In [29]:
last_matrix_new = np.concatenate((last_matrix_new, art_in_prov_not_in_last))
precio_base_array = np.concatenate((precio_base_array, art_in_last_not_in_prov))

Comparar precio medio de proveedores y ultimo precio de compra


In [30]:
precio_base_final_array = []
for row_num in range(precio_base_array.shape[0]):
    row = precio_base_array[row_num,:]
    name = row[0]
    cost = float(row[1])
    if name in last_matrix_new[:,0]:
        loc = np.where(last_matrix_new[:,0]==name)[0][0]
        cost_2 = float(last_matrix_new[loc,1])
        cost_dif = abs((cost-cost_2)/np.min([cost, cost_2]))
        if cost_dif > 5 :
            alertas_dict[name] = 'diferencia grande entre precio de compra y precios de proveedor'
        elif cost_dif > 0.5 :
            alertas_dict[name] = 'diferencia pequeña entre precio de compra y precios de proveedor'
        cost_final = np.mean([cost, cost_2])
        precio_base_final_array.append([name, cost_final])
    else:
        print('ESTE ERROR NUNCA PUEDE APARECER. AVISAR A SIRO')
        print('ERROR', name, 'NO ENCONTRADO')
        
precio_base_final_array = np.array(precio_base_final_array)

Crear PVP nuevos


In [31]:
def factor_precio(f_01, f_1000):
    slope = (f_1000-f_01)/4
    f_0 = f_01 + slope
    def tempfun(x):
        fun = f_0 + slope * np.log10(x)
        fun2 = np.maximum(fun, f_1000)
        fun3 = np.minimum(fun2, f_01)
        return fun3
    return tempfun

In [32]:
def precios(precio_compra):
    p3_factor = factor_precio(3, 1.3)(precio_compra)
    p3 = precio_compra * p3_factor
    p1 = 2.1 * p3
    p2_factor = factor_precio(1.4, 1.15)(p1)
    p2 = p1 * p2_factor
    p4_factor = factor_precio(2, 1.4)(p1)
    p4 = p1 * p4_factor
    return [p1, p2, p3, p4]

In [33]:
precio_base_final = np.array(precio_base_final_array[:,1], dtype='float64')

In [34]:
bool_array.shape[0]


Out[34]:
14686

In [35]:
precio_base_final.shape[0]


Out[35]:
12809

In [36]:
articulos_pvp_nuevo_array = np.round(np.array(precios(precio_base_final)).T,decimals=2)

Comparar PVP nuevo y antiguo


In [37]:
articulos_pvp_array_diferencias = np.zeros_like(articulos_pvp_array)
articulos_pvp_nuevo_array_coherent = np.zeros_like(articulos_pvp_array)

articulos_pvp_array_diferencias[:,0] = articulos_pvp_array[:,0]
articulos_pvp_nuevo_array_coherent[:,0] = articulos_pvp_array[:,0]

articulos_pvp_no_encontrado = []
alerta_grave_dict = {}

for row in range(precio_base_final_array.shape[0]):
    name = precio_base_final_array[row,0]
    pvp_nuevo = articulos_pvp_nuevo_array[row,:]    
    if name in articulos_pvp_array[:,0]:
        loc = np.where(articulos_pvp_array[:,0]==name)[0][0]
        pvp_viejo = np.array(articulos_pvp_array[loc,1:], dtype='float64')
        
        diferencia = np.round((100 * (pvp_nuevo-pvp_viejo) / pvp_viejo), decimals=2)
        articulos_pvp_array_diferencias[loc,1:] = diferencia
        articulos_pvp_nuevo_array_coherent[loc,1:] = pvp_nuevo
    else:
        articulos_pvp_no_encontrado.append([name].append(list(pvp_nuevo)))


C:\Anaconda3\lib\site-packages\ipykernel\__main__.py:17: RuntimeWarning: divide by zero encountered in true_divide

In [38]:
for row in range(articulos_pvp_array.shape[0]):

    name = articulos_pvp_array[row,0]
    pvp_nuevo = articulos_pvp_nuevo_array_coherent[row,1:]
    pvp_viejo = articulos_pvp_array[row, 1:]
    if (np.sum(pvp_nuevo) == 0) and (np.sum(pvp_viejo) != 0) :
        
        alerta_grave_dict[name] = 'PRECIO NUEVO NO CALCULADO: PRECIO NO MODIFICADO'
        articulos_pvp_nuevo_array_coherent[row,1:] = pvp_viejo

In [39]:
pvp_arrays_list = [articulos_pvp_array, articulos_pvp_nuevo_array_coherent, articulos_pvp_array_diferencias]

In [40]:
precios_dato_array = np.zeros([articulos_pvp_array.shape[0],3])

In [41]:
for row in range(articulos_pvp_array.shape[0]):
    name = articulos_pvp_array[row,0]    
    if name in last_matrix[:,0]:
        loc = np.where(last_matrix[:,0]==name)[0][0]
        precios_dato_array[row,2] = last_matrix[loc,1] 
    if name in precio_compra_array[:,0]:
        loc = np.where(precio_compra_array[:,0]==name)[0][0]
        precios_dato_array[row,1] = precio_compra_array[loc,1]
    if name in precio_base_final_array[:,0]:
        loc = np.where(precio_base_final_array[:,0]==name)[0][0]
        precios_dato_array[row,0] = precio_base_final_array[loc,1]

In [42]:
data_to_save = [['art', 
                 'pvp1 ant', 'pvp1 nuev', '% difer',
                 'pvp2 ant', 'pvp2 nuev', '% difer',
                 'pvp3 ant', 'pvp3 nuev', '% difer',
                 'pvp4 ant', 'pvp4 nuev', '% difer',
                 'P Base', 'P Proveedor', 'P compra']]

for row in range(articulos_pvp_array.shape[0]):
#for row in range(20):
    name = articulos_pvp_array[row, 0]
    new_row = [name]
    for pvp_num in range(4):
        for table in range(3):
            cost = pvp_arrays_list[table][row, pvp_num + 1]
            new_row.append(cost)
    for ii in range(3):
        cost = precios_dato_array[row, ii]
        new_row.append(cost)
    
    data_to_save.append(new_row)    
data_to_save_array = np.array(data_to_save[1:])

Corregimos los datos con el tipo de artículo


In [43]:
prov_temp = prov_matrix[np.where(prov_matrix[:,0] == 'P385' )[0]]
found_prov = prov_temp[np.where(prov_temp[:,1] == '0240D010BN4HC')[0]]
found_prov


Out[43]:
array([['P385', '0240D010BN4HC', 46.67]], dtype=object)

In [44]:
bool(found_prov.shape[0])


Out[44]:
True

In [45]:
found_prov[0,2]


Out[45]:
46.67

In [46]:
cost


Out[46]:
35.640000000000001

In [47]:
notas = []
error_array =['']
for row in range(bool_array.shape[0]):    
    name = articulos_pvp_array[row, 0]
    if not bool_array[row]:
        prov = prov_array[row]
        if prov == '':
            notas.append('Artículo de no Autoprecio sin proveedor asignado')
            error_array.append(True)
            continue
        prov_temp = prov_matrix[np.where(prov_matrix[:,0] == prov )[0]]
        found_prov = prov_temp[np.where(prov_temp[:,1] == name)[0]]
        if not bool(found_prov.shape[0]):
            notas.append('Artículo no encontrado en el catálogo del proveedor asignado: '+str(prov))
            error_array.append(True)
            continue
        data_to_save_array[row, 1:7]=0
        data_to_save_array[row, 10:13]=0
        cost = found_prov[0,2]
        cost_2 = float(data_to_save_array[row,7])
        data_to_save_array[row,8] = cost
        data_to_save_array[row,9] = np.round(100*(cost - cost_2)/cost_2, decimals=2)
        notas.append('Precio de proveedor: '+str(prov))
    else:
        notas.append('')
    error_array.append(False)

In [48]:
new_wb = Workbook()

In [49]:
ws = new_wb.active

In [50]:
data_to_save = [['art', 
                 'pvp1 ant', 'pvp1 nuev', '% difer',
                 'pvp2 ant', 'pvp2 nuev', '% difer',
                 'pvp3 ant', 'pvp3 nuev', '% difer',
                 'pvp4 ant', 'pvp4 nuev', '% difer',
                 'P Base', 'P Proveedor', 'P compra']]
for row in range(bool_array.shape[0]):
    line = []
    for cell in data_to_save_array[row, :]:
        line.append(cell)
    line.append(notas[row])
    data_to_save.append(line)

In [51]:
for row in range(len(data_to_save)):
    
    ws.append(data_to_save[row])

Poniendo Bonito el Excel


In [52]:
def rgb(red, green, blue):
    '''Crea código rgb a partir de porcentajes'''
    if red < 0 : red = 0
    if red > 100 : red = 100
    if green < 0 : green = 0
    if green > 100 : green = 100
    if blue < 0 : blue = 0
    if blue > 100 : blue = 100
        
    r = round(red * 2.55)    
    g = round(green * 2.55)    
    b = round(blue * 2.55)
    
    r = hex(r)[-2:]
    g = hex(g)[-2:]
    b = hex(b)[-2:]
    
    return 'ff' + r + g + b

In [53]:
def relleno(r,g,b):
    rell = PatternFill(start_color=rgb(r,g,b),
                   end_color=rgb(r,g,b),
                   fill_type='solid')
    return rell

In [54]:
def coord_excel(row,col):
    str_key ='ABCDEFGHIJKLMNOPQRSTUVW'
    return str_key[col] + str(row+1)

In [73]:
rojo = relleno(100,60,60)
verde_claro = relleno(90,98,94)
naranja_claro = relleno(98, 90, 80)
naranja = relleno(98, 80, 60)
rojo_claro = relleno(100, 85, 85)
verde_oscuro = relleno(70,90,80)
gris_oscuro = relleno(70,70,70)
gris_claro = relleno(90,90,90)
verde_mas_oscuro = relleno(40,80,60)
azul_claro = relleno(90,95,100)
azul_oscuro = relleno(70,70,90)
marron = relleno(85,75,40)

In [56]:
ws.column_dimensions['A'].width = 30
str_key ='ABCDEFGHIJKLMNOPQRSTUVW'
for col in range(1, 16):
    col_name = str_key[col]
    ws.column_dimensions[col_name].width = 7

In [57]:
for pvp_num in range(4):
    for row in range(1,len(data_to_save)):
        col = 3 * (pvp_num + 1)
        cell = ws[coord_excel(row,col)]
        diff = float(cell.value)
        color = verde_claro
        if diff > 500 :
            color = rojo
        elif diff > 100 :
            color = verde_mas_oscuro
        elif diff > 20 :
            color = verde_oscuro
        elif diff < -50 :
            color = naranja
        elif diff < 0 :
            color = naranja_claro
        
        for ii in range(3):
            cell = ws[coord_excel(row,col - ii)]
            cell.fill = color

In [58]:
for row in range(1,len(data_to_save)):
    compra =float( ws[coord_excel(row,14)].value)
    prov = float(ws[coord_excel(row,15)].value)
    
    if (compra == 0 or prov == 0):
        for col in range(13, 16):
            cell = ws[coord_excel(row,col)]
            cell.fill = naranja_claro
    else:
        dif = abs(compra - prov)
        minimo = min(compra, prov)
        porcen = 100 * dif / minimo
        if (dif > 0.05 and porcen > 50):
            color = azul_oscuro
        else:
            color = azul_claro
        for col in range(13, 16):
            cell = ws[coord_excel(row,col)]
            cell.fill = color

In [59]:
for row in range(1,len(data_to_save)):
    name = articulos_pvp_array[row-1, 0]
    if name in alerta_grave_dict:
        for col in range(1, 16):
            cell = ws[coord_excel(row,col)]
            cell.fill = gris_oscuro

In [60]:
for row in range(1,len(data_to_save)):
    nulo = True
    for col in range(1, 16):
        cell = ws[coord_excel(row,col)]
        if float(cell.value) != 0 : nulo = False
    if nulo:
        for col in range(1, 16):
            cell = ws[coord_excel(row,col)]
            cell.fill = gris_claro

In [74]:
for row in range(1,len(data_to_save)):
    
    if error_array[row]:
        for col in range(1, 16):
            cell = ws[coord_excel(row,col)]
            cell.fill = marron

In [61]:
thick_border = Border(right=Side(style='thick'))

In [62]:
thick_bottom = Border(bottom=Side(style='thick'))

In [63]:
for pvp_num in range(5):
    for row in range(1,len(data_to_save)):
        col = pvp_num * 3
        cell = ws[coord_excel(row,col)]
        cell.border = thick_border

In [64]:
for col in range(13):
    cell = ws[coord_excel(0,col)]
    cell.border = thick_bottom

In [65]:
ws.freeze_panes = 'A2'

In [66]:
ws.auto_filter.ref = "A1:" + coord_excel(0, len(data_to_save[0]))

In [75]:
new_wb.save('resultado_excel.xlsx')

In [ ]:


In [ ]:


In [ ]: